using System;
using System.Data;
using System.Data.SqlClient;
using Framework.Library;
using Framework.Module.Adsvertising.Lib.Data;

namespace Framework.Module.Adsvertising.Lib.DataAccess
{
	public class PositionDB
	{
		public static DataTable GetAll () 
		{
			DataTable retVal = null;
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
 			
			SqlCommand dbCmd = new SqlCommand("Adv_Positions_GetAll", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;			
			try
			{
				retVal = new DataTable() ;
				SqlDataAdapter da = new SqlDataAdapter(dbCmd);
				da.Fill(retVal);
			}
			finally
			{
				dbConn.Close();
			}
			return retVal; 
		}
		public static int GetIDByPosition(string _position)
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Adv_Positions_GetIDByPosition", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Position",_position);
			try 
			{
				dbConn.Open();
				SqlDataReader dr= dbCmd.ExecuteReader();
				if (dr.Read()) return dr.GetInt32(0);
				else return 0;				
			}
			finally 
			{
				dbConn.Close();
			}
		}
		public static PositionInfo GetInfoByPosition(string _position)
		{
			return GetInfo(GetIDByPosition(_position));
		}
 
		public static void Delete (int _Pos_ID) 
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Adv_Positions_Delete", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Pos_ID",_Pos_ID);
			try 
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			finally 
			{
				dbConn.Close();
			}
		}
 
		public static int Insert (PositionInfo _posInfo) 
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Adv_Positions_Insert", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Pos_Name",_posInfo.Pos_Name);
			dbCmd.Parameters.AddWithValue("@Pos_Position",_posInfo.Pos_Position);			
			dbCmd.Parameters.AddWithValue("@Pos_Type",_posInfo.Pos_Type);
			dbCmd.Parameters.AddWithValue("@Pos_Width",_posInfo.Pos_Width);
			dbCmd.Parameters.AddWithValue("@Pos_Height",_posInfo.Pos_Height);
			dbCmd.Parameters.AddWithValue("@Pos_SeparateCode",_posInfo.Pos_SeparateCode);			
			dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
			try 
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return (int)dbCmd.Parameters["@RETURN_VALUE"].Value;
			}
			finally 
			{
				dbConn.Close();
			}
		}
 
		public static void Update (PositionInfo _posInfo) 
		{
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Adv_Positions_Update", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Pos_ID",_posInfo.Pos_ID);
			dbCmd.Parameters.AddWithValue("@Pos_Name",_posInfo.Pos_Name);
			dbCmd.Parameters.AddWithValue("@Pos_Position",_posInfo.Pos_Position);			
			dbCmd.Parameters.AddWithValue("@Pos_Type",_posInfo.Pos_Type);
			dbCmd.Parameters.AddWithValue("@Pos_Width",_posInfo.Pos_Width);
			dbCmd.Parameters.AddWithValue("@Pos_Height",_posInfo.Pos_Height);
			dbCmd.Parameters.AddWithValue("@Pos_SeparateCode",_posInfo.Pos_SeparateCode);			
			try 
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
			}
			finally 
			{
				dbConn.Close();
			}
		}
 
		public static PositionInfo GetInfo ( int _Pos_ID ) 
		{
			PositionInfo retVal = null;
			SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
			SqlCommand dbCmd = new SqlCommand("Adv_Positions_GetInfo", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			dbCmd.Parameters.AddWithValue("@Pos_ID", _Pos_ID);
			try 
			{
				dbConn.Open();
				SqlDataReader dr = dbCmd.ExecuteReader();
				if (dr.Read()) 
				{
					retVal = new PositionInfo();
					retVal.Pos_ID=Convert.ToInt32(dr["Pos_ID"]);
					retVal.Pos_Name=Convert.ToString(dr["Pos_Name"]);
					retVal.Pos_Position = Convert.ToString(dr["Pos_Position"]);					
					retVal.Pos_Type=Convert.ToString(dr["Pos_Type"]);
					retVal.Pos_Width = Convert.ToInt32(dr["Pos_Width"]);
					retVal.Pos_Height = Convert.ToInt32(dr["Pos_Height"]);
					retVal.Pos_SeparateCode  = Convert.ToString(dr["Pos_SeparateCode"]);					
				}
				dr.Close();
			}
			finally
			{				
				dbConn.Close();
			}
			return retVal;
		}
 
	}
}

